package cn.com.bluemoon.daps.system.utils;


import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.com.bluemoon.daps.common.domain.ResultBean;
import cn.com.bluemoon.daps.common.exception.DapException;
import cn.com.bluemoon.daps.system.entity.DapDataModelNameRecommendation;
import com.google.common.collect.Lists;
import freemarker.template.utility.RichObjectWrapper;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.StopWatch;
import org.springframework.web.multipart.MultipartFile;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Array;
import java.lang.reflect.Field;
import java.util.*;
import java.util.regex.Pattern;

import static org.apache.poi.ss.usermodel.CellType.ERROR;
import static org.apache.poi.ss.usermodel.CellType.NUMERIC;

public class ImportDictionaryExcel {

    private static Logger logger  = LoggerFactory.getLogger(ImportDictionaryExcel.class);
    private final static String xls = "xls";
    private final static String xlsx = "xlsx";
    private final static Integer NUM = 0;
    // 全英文
    private final static String ENGLISH = "^[A-Za-z]+$";
    // 全中文
    private final static String CHINESE = "^[\\u4e00-\\u9fa5]{0,}$";

    /**
     * 进行解析
     * @param file
     * @return
     */
    public static ResultBean<?> readExcel(MultipartFile file){

        Workbook workbook = null;
        //检查文件
        final StopWatch sw = new StopWatch();
        try {
            sw.start("检查文件的正确性");
            checkFile(file);
            workbook = getWorkBook(file);
        } catch (IOException e) {
            throw new DapException(e.getMessage());
        }
        // 只需获得第一个sheet页
        Sheet sheet = workbook.getSheetAt(NUM);
        if(sheet == null){
            return ResultBean.error("模板格式不正确");
        }
        // 封装错误信息
        List<String> errorList = Lists.newArrayList();
        // 表头数据
        checkTableHeader(sheet.getRow(NUM), errorList);
        //读取数据 循环所有行
        List<Map<String, Object>> content = Lists.newArrayList();
        for(int rowNum = 1;rowNum <= sheet.getLastRowNum();rowNum++){
            //获得当前行
            Row row = sheet.getRow(rowNum);
            if(row == null){
                continue;
            }
            //获得当前行的开始列
            int firstCellNum = sheet.getRow(NUM).getFirstCellNum();
            //获得当前行的列数
            int lastCellNum = sheet.getRow(NUM).getPhysicalNumberOfCells();
            Map<String, Object> values = new HashMap<>();
            //循环当前行
            for(int cellNum = firstCellNum; cellNum < lastCellNum;cellNum++){
                Cell cell = row.getCell(cellNum);
                String key = TableHeaderConstant.HEADER_LIST.get(cellNum);
                CheckTableContent(cellNum, rowNum, getCellValue(cell), errorList);
                if(cellNum == 0){
                    Integer type = getCellValue(cell).equals(TableHeaderConstant.TYPE_LIST.get(cellNum)) ? 1 :
                            getCellValue(cell).equals(TableHeaderConstant.TYPE_LIST.get(1)) ? 2 : 3;
                    values.put(key, type);
                }else{
                    values.put(key, getCellValue(cell));
                }
            }
            content.add(values);
        }
        // 错误信息不为空
        return errorList.isEmpty()? ResultBean.ok(content) : ResultBean.error(errorList);
    }

    /**
     * 检查表格内容
     * @param cellNum
     * @param rowNum
     * @param cellValue
     * @param errorList
     */
    public static void CheckTableContent(Integer cellNum, Integer rowNum, String cellValue, List<String> errorList){

        if(cellNum == 0 || cellNum == 1 || cellNum == 2 || cellNum == 3){
            if(StringUtils.isEmpty(cellValue)){
                errorList.add( String.format("第%s行，第%s列不允许为空", rowNum + 1, cellNum + 1));
            }else{
                if(cellNum == 0 && !TableHeaderConstant.TYPE_LIST.contains(cellValue)){
                    errorList.add(String.format("第%s行，第%s列的类型错误", rowNum + 1, cellNum + 1));
                }
                // 是否为中文
                if(cellNum == 1 && !Pattern.matches(CHINESE, cellValue)){
                    errorList.add(String.format("第%s行，第%s列必须为中文", rowNum + 1, cellNum + 1));
                }
                if((cellNum == 2 || cellNum ==3) && !Pattern.matches(ENGLISH, cellValue)){
                    errorList.add(String.format("第%s行，第%s列必须为英文", rowNum + 1, cellNum + 1));
                }
            }
        }
    }

    public static String getCellValue(Cell cell){

        String cellValue = "";
        if(cell == null){
            return cellValue;
        }
        //把数字当成String来读，避免出现1读成1.0的情况
        if(cell.getCellType() == NUMERIC){
            cell.setCellType(CellType.STRING);
        }
        //判断数据的类型
        switch (cell.getCellType()){
            case NUMERIC: //数字
                cellValue = String.valueOf(cell.getNumericCellValue());
                break;
            case STRING: //字符串
                cellValue = String.valueOf(cell.getStringCellValue());
                break;
            case BOOLEAN: //Boolean
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case FORMULA: //公式
                cellValue = String.valueOf(cell.getCellFormula());
                break;
            case BLANK: //空值
                cellValue = "";
                break;
            case ERROR: //故障
                cellValue = "非法字符";
                break;
            default:
                cellValue = "未知类型";
                break;
        }
        return cellValue;
    }


    public static List<String> checkTableHeader(Row row, List<String> errorList){

        List<String> list = Lists.newArrayList();
        for(int begin = row.getFirstCellNum(); begin < row.getLastCellNum(); begin++){
            if(StringUtils.isBlank(row.getCell(begin).getStringCellValue())){
                String format = String.format("第1行，第%s列表头数据为空", begin + 1);
                errorList.add(format);
            }
        }
        return list;
    }

    public static void checkFile(MultipartFile file) throws IOException{
        //判断文件是否存在
        if(null == file){
            logger.error("文件不存在！");
            throw new FileNotFoundException("文件不存在！");
        }
        //获得文件名
        String fileName = file.getOriginalFilename();
        //判断文件是否是excel文件
        if(!fileName.endsWith(xls) && !fileName.endsWith(xlsx)){
            logger.error(fileName + "不是excel文件");
            throw new IOException(fileName + "不是excel文件");
        }
    }

    public static Workbook getWorkBook(MultipartFile file) throws IOException {
        //获得文件名
        String fileName = file.getOriginalFilename();
        //创建Workbook工作薄对象，表示整个excel
        Workbook workbook = null;
        InputStream is = null;
        try {
            //获取excel文件的io流
            is = file.getInputStream();
            //根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
            if(fileName.endsWith(xls)){
                //2003
                workbook = WorkbookFactory.create(is);
            }else if(fileName.endsWith(xlsx)){
                //2007 及2007以上
                workbook = WorkbookFactory.create(is);
            }
        } catch (IOException e) {
            logger.info(e.getMessage());
        } finally {
            if (is != null) {
                is.close();
            }
        }
        return workbook;
    }

    public boolean checkContent(String str, boolean flag){
        // 全英文
        String english = "^[A-Za-z]+$";
        // 全中文
        String chinese = "^[\\u4e00-\\u9fa5]{0,}$";
        return Pattern.matches(flag ? english : chinese, str);

    }
}
